package database;

import java.io.IOException;
import java.net.UnknownHostException;
import java.sql.Statement;
import java.sql.*;
import java.text.*;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.util.logging.*;
import javax.servlet.jsp.JspWriter;

/**
 *
 * @author Abu Nasser#
 */
public class DBCon {

    static String driver = "com.mysql.jdbc.Driver";
    static String url = "jdbc:mysql://localhost:3306/spms";
    static String username = "SPMS";
    static String password = "SPMS";
    Statement statement;
    public int whoAmI = -2;
    JspWriter out = null;
    public Connection connection;

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBCon.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public DBCon() {
        try {
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DBCon.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public DBCon(JspWriter out) {
        try {
            this.out = out;
            connection = DriverManager.getConnection(url, username, password);
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DBCon.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean isUsernameExist(String username) throws SQLException {
        String query = "Select count(*) a From userlogin where username = '" + username + "'";
        ResultSet res = statement.executeQuery(query);
        res.next();
        if (res.getInt(1) > 0) {
            return true;
        } else {
            return false;
        }
    }

    public boolean haveEnrolmentKey(boolean isStudent, String key) throws SQLException {
        String query = "Select * From enrolmentkey";
        ResultSet res = statement.executeQuery(query);
        res.next();
        if (res.getString((isStudent ? "student_key" : "Professor_key")).equals(key)) {
            return true;
        } else {
            return false;
        }

    }

    public int insertStudent(String firstName, String fatherName, String lastName, String GPA, String mobileNumber,
            String email, String username, String password, String affilation, String enrolment) throws SQLException {

        SimpleDateFormat time = new SimpleDateFormat("hh:mm:ss a");
        SimpleDateFormat day = new SimpleDateFormat("MM/dd/yyyy");
        String eventDate = day.format(new java.util.Date());
        String eventTime = time.format(new java.util.Date());


        if (isUsernameExist(username)) {
            return 1;
        } else if (!haveEnrolmentKey(true, enrolment)) {
            return 2;
        } else {
            String query = "INSERT INTO userlogin (username ,password, type, status, creation_date) VALUES ('" + username + "', '" + password + "', 'student', 'pending','" + eventDate + "  " + eventTime + "')";

            // Execute query and save results.
            int res = statement.executeUpdate(query);

            query = "INSERT INTO student (FirstName, FatherName, LastName, GPA, MobilePhone, Email, UserName,aff) VALUES ('" +
                    firstName + "', '" + fatherName + "', '" + lastName + "', " + GPA + ", '" + mobileNumber + "', '" + email + "', '" + username + "','" + affilation + "')";
            // Execute query and save results.
            res = statement.executeUpdate(query);
            return 0;
        }
    }

    public int insertProfessor(String firstName, String middlename, String lastName, String deparment,
            String degree, String email, String workPhone, String mobilePhone,
            String homePage, String username, String password, String enrolment) throws SQLException {

        SimpleDateFormat time = new SimpleDateFormat("hh:mm:ss a");
        SimpleDateFormat day = new SimpleDateFormat("MM/dd/yyyy");
        String eventDate = day.format(new java.util.Date());
        String eventTime = time.format(new java.util.Date());


        if (isUsernameExist(username)) {
            return 1;
        } else if (!haveEnrolmentKey(false, enrolment)) {
            return 2;
        } else {
            String query = "INSERT INTO userlogin (username ,password, type, status, creation_date) VALUES ('" + username + "', '" + password + "', 'professor', 'accepted','" + eventDate + "  " + eventTime + "')";

            // Execute query and save results.
            int res = statement.executeUpdate(query);

            query = "INSERT INTO professor (FirstName, middlename,LastName, department, degree, Email, workphone,mobilephone,homepage,UserName) VALUES ('" +
                    firstName + "', '" + middlename + "', '" + lastName + "', '" + deparment + "', '" + degree + "', '" + email + "', '" + workPhone + "', '" + mobilePhone + "', '" + homePage + "', '" + username + "')";
            // Execute query and save results.
            res = statement.executeUpdate(query);
            return 0;
        }
    }

    public boolean insertProject(String Title, String Description, String Project_Objectives, String Required_Background, String Tools_Needed, String Difficulty_Level, String Project_Type, String Project_Keywords, String Expected_Results, String Additional_Comments, String ProfessorId) throws SQLException {

        if (!isProjectExist(Title)) {
            String query = "INSERT INTO project (Title,Description,Project_Objectives,Required_Background,Tools_Needed,Difficulty_Level,Project_Type,Project_Keywords,Expected_Results,Additional_Comments,ProfessorId) values ('" + Title + "','" + Description + "','" + Project_Objectives + "','" + Required_Background + "','" + Tools_Needed + "','" + Difficulty_Level + "','" + Project_Type + "','" + Project_Keywords + "','" + Expected_Results + "','" + Additional_Comments + "'," + ProfessorId + ")";
            // Execute query and save results.
            statement.executeUpdate(query);
            return true;
        } else {
            return false;
        }
    }

    public boolean insertProject(String Title, String Description, String Project_Objectives, String Required_Background, String Tools_Needed, String Difficulty_Level, String Project_Type, String Project_Keywords, String Expected_Results, String Additional_Comments, String status, String ProfessorId) throws SQLException, IOException {

        if (!isProjectExist(Title)) {
            String query = "INSERT INTO project (Title,Description,Project_Objectives,Required_Background,Tools_Needed,Difficulty_Level,Project_Type,Project_Keywords,Expected_Results,Additional_Comments,Status,ProfessorId) values ('" + Title + "','" + Description + "','" + Project_Objectives + "','" + Required_Background + "','" + Tools_Needed + "','" + Difficulty_Level + "','" + Project_Type + "','" + Project_Keywords + "','" + Expected_Results + "','" + Additional_Comments + "','" + status + "'," + ProfessorId + ")";
            // Execute query and save results.\
            out.print(query);
            statement.executeUpdate(query);
            return true;
        } else {
            return false;
        }
    }

    public boolean insertProject(String... att) throws SQLException {

        if (!isProjectExist(att[0])) {
            String query = "INSERT INTO project (Title,Descr,Objective,Req,Tools,Difficulty,Type,keyword,Results,Comment,ProfessorId) values ('" + att[0] + "','" + att[1] + "','" + att[2] + "','" + att[3] + "','" + att[4] + "','" + att[5] + "','" + att[6] + "','" + att[7] + "','" + att[8] + "','" + att[9] + "'," + att[10] + ")";
            // Execute query and save results.
            int res = statement.executeUpdate(query);
            return true;
        }
        return false;
    }

    public ResultSet login(String username, String password) throws SQLException, UnknownHostException {

        ResultSet rs = statement.executeQuery("select * from userlogin where UserName = '" + username + "'");

        String permission;
        if (rs.next()) {
            String query = "select * from userlogin where UserName = '" + username + "' and password = '" + password + "'";

            ResultSet res = statement.executeQuery(query);
            if (res.next()) {
                if (res.getString("status").equals("pending")) {
                    whoAmI = -1;
                    return null;
                } else if (res.getString("type").equals("student")) {
                    permission = getControlValue("student_login") + "";
                    if (permission.equals("false")) {
                        whoAmI = -3;
                        return null;
                    }
                    setEvent("Username: " + username + " is login", "login successful", "login");
                    lastlogin(username);
                    query = "select * from student where UserName = '" + username + "'";
                    whoAmI = 1;
                    return statement.executeQuery(query);
                } else if (res.getString("type").equals("professor")) {
                    permission = getControlValue("professor_login") + "";
                    if (permission.equals("false")) {
                        whoAmI = -3;
                        return null;
                    }
                    lastlogin(username);
                    setEvent("Username: " + username + " is login", "login successful", "login");
                    query = "select * from professor where UserName = '" + username + "'";
                    whoAmI = 2;
                    return statement.executeQuery(query);
                } else if (res.getString("type").equals("committee")) {
                    permission = getControlValue("professor_login") + "";
                    if (permission.equals("false")) {
                        whoAmI = -3;
                        return null;
                    }
                    lastlogin(username);
                    setEvent("Username: " + username + " is login", "login successful", "login");
                    query = "select * from professor where UserName = '" + username + "'";
                    whoAmI = 3;
                    return statement.executeQuery(query);
                } else if (res.getString("type").equals("admin")) {
                    lastlogin(username);
                    setEvent("Username: " + username + " is login", "login successful", "login");
                    whoAmI = 4;
                    return res;
                } else {
                    setEvent("Username: " + username + " is login", "login faild, unknown login type", "login");
                    System.out.println("Something error in database (username creator not exist)!");
                    return null;
                }
            } else {
                whoAmI = -2;
                setEvent("Username: " + username + " tried to login", "login faild, wrong password ", "login");
                return null;
            }
        } else {
            setEvent("Username: " + username + " tried to login", "login faild, unknown username ", "login");
            return null;
        }
    }

    public ResultSet getProject(String where) throws SQLException {
        String query = "select * from project " + where;
        return statement.executeQuery(query);
    }

    public ResultSet getSProject(String sId) throws SQLException {
        String query = "select p.* from project p, student s where s.id = " + sId + " and p.id = s.projid";
        return statement.executeQuery(query);
    }

    public ResultSet getStudent(String where) throws SQLException {
        String query = "select * from student " + where;
        return statement.executeQuery(query);
    }

    public ResultSet getStudents() throws SQLException {
        return statement.executeQuery("select * from student ");
    }

    public int countStudent(String where) throws SQLException {
        String query = "select count(*) from student " + where;
        ResultSet res = statement.executeQuery(query);
        res.next();
        return res.getInt(1);
    }

    public ResultSet getProf(String where) throws SQLException {
        String query = "select * from professor " + where;
        return statement.executeQuery(query);
    }

    public int deleteChoosedProject(String where) throws SQLException {
        String query = "DELETE FROM choosedprojects " + where;
        return statement.executeUpdate(query);
    }

    public int deleteProject(String id) throws SQLException {
        statement.executeUpdate("DELETE FROM choosedprojects where projectid =" + id);
            return statement.executeUpdate("DELETE FROM project where id =" + id);
        
    }

    public int insertProjectChoose(String prjId, String stId, String priority) throws SQLException {
        String query = "INSERT INTO choosedprojects VALUES (" + prjId + "," + stId + "," + priority + ")";
        return statement.executeUpdate(query);
    }

    public ResultSet getProjectsValidated() throws SQLException {
        String query = "select * from project, professor WHERE status = 'accepted' AND project.ProfessorID = professor.ID";
        return statement.executeQuery(query);
    }

    public ResultSet getAllProjects() throws SQLException {
        String query = "select * from project, professor WHERE status <> 'uncompleted' AND project.ProfessorID = professor.ID order by status";
        return statement.executeQuery(query);
    }

    public ResultSet getSelectedProject(String projID) throws SQLException {
        return statement.executeQuery("select * from project, professor WHERE project.ProfessorID = professor.ID AND project.ID = '" + projID + "'");
    }

    public int countProjects(String where) throws SQLException {
        String query = "select count(*) from project " + where;
        ResultSet res = statement.executeQuery(query);
        res.next();
        return res.getInt(1);
    }

    public ResultSet getProjectsChoosed(String where) throws SQLException {
        String query = "SELECT * from project, choosedprojects " + where;
        return statement.executeQuery(query);
    }

    public ResultSet getChoosedProjects(String where) throws SQLException {
        String query = "SELECT * from choosedprojects " + where;
        return statement.executeQuery(query);
    }

    public int countChoosedProjects(String where) throws SQLException {
        String query = "SELECT count(*) from choosedprojects " + where;
        ResultSet res = statement.executeQuery(query);
        res.next();
        return res.getInt(1);
    }

    public void updateStudentsProject(String projId, String where) throws SQLException {
        String query = "UPDATE student set projId = " + projId + where;
        statement.executeUpdate(query);
    }

    public int countProjectsChoosed(String where) throws SQLException {
        String query = "SELECT count(*) from project, choosedprojects " + where;
        ResultSet res = statement.executeQuery(query);
        res.next();
        return res.getInt(1);
    }

    public int updateStudent(String firstName, String fatherName, String lastName, String GPA, String mobileNumber,
            String email, String username, String password, String affilation, String enrolment) throws SQLException, IOException {

        String query = "UPDATE student set FirstName= '" + firstName + "', fathername = '" + fatherName + "', lastname = '" + lastName + "', gpa =" + GPA + ", mobilephone = '" + mobileNumber + "', email = '" + email + "', username = '" + username + "',aff = '" + affilation + "' where username = '" + username + "'";
        out.print(query);
        if (statement.executeUpdate(query) == 1) {
            query = "UPDATE userlogin set password = '" + password + "' where username = '" + username + "'";
            out.print(query);
            return statement.executeUpdate(query);
        }
        return 0;
    }

    public Statement getStatement() {
        return statement;
    }

    public boolean isProjectExist(String title) throws SQLException {
        String query = "Select count(*) From project where title = '" + title + "'";
        ResultSet res = statement.executeQuery(query);
        res.next();
        if (res.getInt(1) > 0) {
            return true;
        } else {
            return false;
        }
    }

    public String[] getEnrolmentKey() {
        ResultSet res = null;
        String[] enrol = new String[2];
        try {
            res = statement.executeQuery("select * from enrolmentkey");
            res.next();

            enrol[0] = res.getString(1);
            enrol[1] = res.getString(2);


        } catch (SQLException ex) {
            Logger.getLogger(DBCon.class.getName()).log(Level.SEVERE, null, ex);
        }
        return enrol;
    }

    public void updateProfessorType(String username, String type) throws SQLException {
        statement.executeUpdate("UPDATE userlogin set type ='" + type + "' where username='" + username + "'");
    }

    public ResultSet getUsers(String where) throws SQLException {
        String query = "SELECT * from userlogin " + where;
        return statement.executeQuery(query);
    }

    public int countUsers(String where) throws SQLException {
        String query = "SELECT count(*) from userlogin " + where;
        ResultSet re = statement.executeQuery(query);
        re.next();
        return re.getInt(1);
    }

    public void updateChoices(String id, String... choices) throws SQLException {
        statement.executeUpdate("DELETE FROM choosedprojects where studentid = " + id);
        for (int i = 0; i < choices.length && choices[i] != null; i++) {
            statement.executeUpdate("INSERT INTO choosedprojects Values(" + choices[i] + "," + id + "," + i + ");");
        }
    }

    public int updateProfessor(
            String fn,
            String mn,
            String ln,
            String dpt,
            String deg,
            String email,
            String wp,
            String mp,
            String hp,
            String userName,
            String password) throws SQLException, IOException {


        String query = "UPDATE professor set FirstName= '" + fn + "' , MiddleName= '" + mn + "' , lastname='" + ln + "', department='" + dpt + "', degree='" + deg + "' , Email='" + email + "' , workPhone='" + wp + "' , mobilePhone='" + mp + "' , homePage='" + hp + "' where userName='" + userName + "'";
        out.print(query);
        if (statement.executeUpdate(query) == 1) {
            query = "UPDATE userlogin set password= '" + password + "' where userName = '" + userName + "'";
            out.print(query);
            return statement.executeUpdate(query);
        }
        return -1;
    }

    public void updateEnrolmentKey(String stKey, String pfKey) throws SQLException {
        statement.executeUpdate("UPDATE enrolmentkey set student_key ='" + stKey + "', Professor_key='" + pfKey + "'");
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        connection.close();
    }

    public int getEventCount() throws SQLException {
        ResultSet rs = statement.executeQuery("select count(*) from events");
        rs.next();

        return rs.getInt(1);
    }

    public ResultSet getEvent() throws SQLException {
        return statement.executeQuery("select * from events");
    }

    public String getAdminPass() throws SQLException {
        ResultSet rs = statement.executeQuery("select password from userlogin where username='admin'");
        rs.next();
        return rs.getString(1);
    }

    public void setAdminPass(String newPass) throws SQLException {
        statement.executeUpdate("Update userlogin set password ='" + newPass + "' where username='admin'");
    }

    public void updateProfessorStatus(String username, String status) throws SQLException {
        statement.executeUpdate("UPDATE userlogin set status ='" + status + "' where username='" + username + "'");
    }

    public void updateProjectStatus(String id, String status) throws SQLException {
        statement.executeUpdate("UPDATE project set status ='" + status + "' where id='" + id + "'");
    }

    public int getNewsCount() throws SQLException {
        ResultSet rs = statement.executeQuery("select count(*) from news");
        rs.next();
        return rs.getInt(1);
    }

    public ResultSet getNews() throws SQLException {
        return statement.executeQuery("select * from news");
    }

    public void setEvent(String title, String desc, String type) throws SQLException, UnknownHostException {

        SimpleDateFormat time = new SimpleDateFormat("hh:mm:ss a");
        SimpleDateFormat day = new SimpleDateFormat("MM/dd/yyyy");

        String eventDate = day.format(new java.util.Date());
        String eventTime = time.format(new java.util.Date());
        String ipAdd = java.net.InetAddress.getLocalHost().getHostAddress();
        ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM events");
        rs.next();
        int counter = rs.getInt(1);

        if (counter > 0) {
            ResultSet rs1 = statement.executeQuery("SELECT MAX(no) FROM events");
            rs1.next();
            int max = rs1.getInt(1);
            counter = max + 1;
        }

        String query = "INSERT INTO events values (" + counter + ",'" + title + "','" + desc + "','" + eventDate + "','" + eventTime + "','" + type + "','" + ipAdd + "')";
        statement.executeUpdate(query);

    }

    public String[] getOwner(String id) throws SQLException {
        ResultSet res = statement.executeQuery("select firstName,lastName from professor where id =" + id);
        String[] name = new String[2];
        res.next();
        name[0] = res.getString(1);
        name[1] = res.getString(2);

        return name;
    }

    public boolean creatProject(String colName, String data) throws SQLException {
        return statement.execute("INSERT INTO project (" + colName + ") values(" + data + ")");
    }

    public ResultSet getStInPrj() throws SQLException {
        return statement.executeQuery("select firstname, lastname, projid from student");
    }

    public void updateProject(String data) throws SQLException {
        statement.executeUpdate("UPDATE project SET " + data);

    }

    public String getProjectStatus(String id) throws SQLException {
        ResultSet res = statement.executeQuery("select status from project where id=" + id);
        res.next();
        return res.getString(1);
    }

    public void lastlogin(String username) throws SQLException {
        SimpleDateFormat time = new SimpleDateFormat("hh:mm:ss a");
        SimpleDateFormat day = new SimpleDateFormat("MM/dd/yyyy");
        String eventDate = day.format(new java.util.Date());
        String eventTime = time.format(new java.util.Date());
        String lastlogin = eventDate + "  " + eventTime;
        statement.executeUpdate("UPDATE userlogin SET last_login = '" + lastlogin + "' where username = '" + username + "'");
    }

    public ResultSet getControl() throws SQLException {
        return statement.executeQuery("select * from permission");
    }

    public void setControl(String data) throws SQLException {
        statement.executeUpdate("update permission set " + data);
    }

    public boolean changeGPA(String id, String GPA) throws SQLException {
        return statement.execute("update Student set gpa=" + GPA + " where id=" + id);
    }

    public String getControlValue(String name) throws SQLException {

        ResultSet res = statement.executeQuery("select value from permission where name = '" + name + "'");
        res.next();
        return res.getString(1);
    }
}


